package Database;

import java.util.ArrayList;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import functions.item;

/**
 * Created with IntelliJ IDEA.
 * User: MoiMini
 * Date: 15/10/13
 * Time: 17:36
 * To change this template use File | Settings | File Templates.
 */
public class Db_handler extends SQLiteOpenHelper {

    // All Static variables
    // Database Version
    private static final int DATABASE_VERSION = 1;

    // Database Name
    private static final String DATABASE_NAME = "itemsManager";

    // items table name
    private static final String TABLE_itemS = "items";

    // items Table Columns names
    private static final String KEY_ID = "id";
    private static final String KEY_NAME = "name";
    private static final String KEY_SKU = "sku";
    private static final String KEY_STOCK = "stock";
    private final ArrayList<item> item_list = new ArrayList<item>();

    public Db_handler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // Creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_itemS_TABLE = "CREATE TABLE " + TABLE_itemS + "("
                + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT,"
                + KEY_SKU + " TEXT," + KEY_STOCK + " INTEGER" + ")";
        db.execSQL(CREATE_itemS_TABLE);
    }

    // Upgrading database
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // Drop older table if existed
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_itemS);

        // Create tables again
        onCreate(db);
    }

    /**
     * All CRUD(Create, Read, Update, Delete) Operations
     */

    // Adding new item
    public void Add_item(item item) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(KEY_NAME, item.getItem_name());
        values.put(KEY_SKU, item.getSku());
        values.put(KEY_STOCK, item.getStock_level());
        // Inserting Row
        db.insert(TABLE_itemS, null, values);
        db.close(); // Closing database connection
    }

    // Getting single item
    item Get_item(String SKU) {
        SQLiteDatabase db = this.getReadableDatabase();

        Cursor cursor = db.query(TABLE_itemS, new String[] { KEY_ID,
                KEY_SKU, KEY_STOCK, KEY_NAME }, KEY_SKU + "=?",
                new String[] { String.valueOf(SKU) }, null, null, null, null);
        if (cursor != null)
            cursor.moveToFirst();

        item item = new item(cursor.getString(1),Integer.parseInt(cursor.getString(2)), cursor.getString(3));
        // return item
        cursor.close();
        db.close();

        return item;
    }

    // Getting All items
    public ArrayList<item> Get_items() {
        try {
            item_list.clear();

            // Select All Query
            String selectQuery = "SELECT  * FROM " + TABLE_itemS;

            SQLiteDatabase db = this.getWritableDatabase();
            Cursor cursor = db.rawQuery(selectQuery, null);

            // looping through all rows and adding to list
            if (cursor.moveToFirst()) {
                do {
                    item item = new item();
                    item.setSku(cursor.getString(2));
                    item.setStock_level(Integer.parseInt(cursor.getString(3)));
                    item.setItem_name(cursor.getString(1));
                    // Adding item to list
                    item_list.add(item);
                } while (cursor.moveToNext());
            }

            // return item list
            cursor.close();
            db.close();
            return item_list;
        } catch (Exception e) {
            // TODO: handle exception
            Log.e("all_item", "" + e);
        }

        return item_list;
    }

    // Updating single item
    public int Update_item(item item) {
        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues values = new ContentValues();
        values.put(KEY_NAME, item.getItem_name());
        values.put(KEY_SKU, item.getSku());
        values.put(KEY_STOCK, item.getStock_level());

        // updating row
        return db.update(TABLE_itemS, values, KEY_SKU + " = ?",
                new String[] { String.valueOf(item.getSku()) });
    }

    // Deleting single item
    public void Delete_item(int id) {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_itemS, KEY_ID + " = ?",
                new String[] { String.valueOf(id) });
        db.close();
    }

    // Getting items Count
    public int Get_Total_items() {
        String countQuery = "SELECT  * FROM " + TABLE_itemS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(countQuery, null);
        cursor.close();

        // return count
        return cursor.getCount();
    }

}
